----Create database with name is LoanManager-----
create database LoanManager
go
use LoanManager
----Create tables----
create table tbl_Accounts
(
	RegisterNo int identity,
	RegisterDate datetime not null,
	Name nvarchar(50) not null,
	Phone nvarchar(30) not null,
	Address nvarchar(50),
	Email nvarchar(70) not null ,
	Organization nvarchar(30),
	[Gross Salary] money not null,
	constraint PK_Acc primary key(RegisterNo)
)

create table tbl_Fines
(
	FineCode int identity,
	FineAmount float default 0,
	constraint PK_Fine primary key(FineCode)
)

create table tbl_Branches
(
	BranchCode int identity,
	Name nvarchar(50) not null,
	Address nvarchar(70) not null,
	Phone nvarchar(20) not null,
	Email nvarchar(50) not null,
	Status bit default 1,
	constraint PK_Branches primary key(BranchCode)
)
create table tbl_LoanType
(
	LoanTypeCode int identity,
	Name nvarchar(50) not null,
	Description nvarchar(100),
	InterestRate int,
	Status bit default 1,
	constraint PK_LoanType primary key(LoanTypeCode)
)
create table tbl_Durations
(
	AccountLoanCode int identity,
	LoanTypeCode int,
	StartDate datetime not null,
	AmountLoan float default 0,
	EndDate datetime not null,
	Balance float,
	LoanStatus bit default 1,
	Accepted bit default 1,
	constraint PK_Duration primary key(AccountLoanCode),
	constraint FK_Duration foreign key (LoanTypeCode) references tbl_LoanType(LoanTypeCode)
)
create table tbl_AccountDetails
(
	RegisterNo int,
	AccountLoanCode int,
	BranchCode int,
	Period int,
	constraint FK_AccountDetails_Accounts foreign key (RegisterNo) references tbl_Accounts(RegisterNo),
	constraint FK_AccountDetails_Branches foreign key(BranchCode) references tbl_Branches(BranchCode),
	constraint FK_AccountDetails_Durations foreign key(AccountLoanCode) references tbl_Durations(AccountLoanCode)
)

create table tbl_Roles
(
	RoleCode int identity,
	Name nvarchar(50) not null,
	LoanTypeCode int,
	constraint PK_Roles primary key(RoleCode),
	constraint FK_Roles_LoanType foreign key(LoanTypeCode) references tbl_LoanType(LoanTypeCode)
)

create table tbl_Admin
(
	AdminID int identity,
	AdminAccount nvarchar(50) not null,
	AdminPass nvarchar(50) not null,
	FullName nvarchar(70),
	Address nvarchar(50),
	Phone nvarchar(20) not null,
	Email nvarchar(80) not null,
	RoleCode int,
	constraint PK_Admin primary key(AdminID),
	constraint FK_Admin_Roles foreign key(RoleCode) references tbl_Roles(RoleCode)
)
create table tbl_DurationDetails
(
	AccountLoanCode int not null,
	FineCode int default 1,
	DateDue datetime not null,
	AmountDue float not null,
	AmountPaid float default 0,
	PaidDate datetime,
	Status bit default 0
	constraint FK_DurationDetails_Duration foreign key(AccountLoanCode) references tbl_Durations(AccountLoanCode),
	constraint FK_DurationDetails_Fines foreign key(FineCode) references tbl_Fines(FineCode)
)

go



----Insert Data ----
insert into tbl_Fines values(0);
insert into tbl_Fines values(0.02);
insert into tbl_Fines values(0.05);
insert into tbl_Fines values(0.08);

insert into tbl_Branches values('Center Point','21/7 Cong Hoa','01675019782','silver@yahoo.com',1);
insert into tbl_Branches values('Silkroad Center','10 Nguyen Hue','0838324233','gold@loansystem.com',1);
insert into tbl_Branches values('Cty Phan Mem','655 Nguyen Thi Minh Khai','0838903423','green@loansystem.com',1);
insert into tbl_Branches values('FSoft','12 Phan Ke Binh','0977450790','yellow@loansystem.com',1);
insert into tbl_Branches values('Red','17/5 Suoi Tien - Thu Duc','09485019782','red@gmail.com',0);

insert into tbl_LoanType values('Home Loan','Housing Finance',10,1);
insert into tbl_LoanType values('Vehicle Loan','Vehicle Finance',13,1);
insert into tbl_LoanType values('Person Loan','Person Finance',18,1);
insert into tbl_LoanType values('Education Loan','Education Finance',5,1);

insert into tbl_Accounts values('08-21-2010','Nguyen Duc Cong','0977459790','Ha Noi','congnd@yahoo.com','FPT',4000);


insert into tbl_Durations values (1,'08-20-2009',200000,'08-20-2010',220000,0,0);


insert into tbl_AccountDetails values(1,1,3,1);


insert into tbl_Roles values('Home Loan',1);
insert into tbl_Roles values('Vehicle Loan',2);
insert into tbl_Roles values('Personal Loan',3);
insert into tbl_Roles values('Education Loan',4);
insert into tbl_Roles values('Admin',null);

insert into tbl_Admin values('Home Loan','12345','Manage Home Loan','200 Nguyen Dinh Chieu','0903200200','fat1@yahoo.com',1);
insert into tbl_Admin values('Vehicle Loan','12345','Manage Vehicle Loan','299 Le Quy Don','0902448842','fat2@yahoo.com',2);
insert into tbl_Admin values('Personal Loan','12345','Manage Personal Loan','200 Nguyen Dinh Chieu','0912150340','fat3@yahoo.com',3);
insert into tbl_Admin values('Education Loan','12345','Manage Education Loan','200 Nguyen Dinh Chieu','0966789034','fatG@yahoo.com',4);
insert into tbl_Admin values('admin','12345','Manage System Loan','200 Nguyen Dinh Chieu','0977450790','congndyahoo.com',5);

insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,1,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,2,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,3,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,4,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,5,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,6,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,7,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,8,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,9,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,10,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,11,GETDATE()),18333.33);
insert into tbl_DurationDetails(AccountLoanCode,DateDue,AmountDue) values(1,DATEADD(MM,12,GETDATE()),18333.33);
select DATEADD(MM,1,GETDATE())
select * from tbl_Accounts
select * from tbl_Fines
select * from tbl_Branches
select * from tbl_LoanType
select * from tbl_Roles
select * from tbl_Admin
go
----CREATE VIEW-------
create view vw_customerList
as
select distinct a.Period,b.Organization,b.RegisterDate,b.RegisterNo,b.Name,b.Address,b.Email,b.[Gross Salary],b.Phone,c.Name as [Branch Name],
				c.Status,d.AmountLoan,d.LoanStatus,d.Accepted
from tbl_AccountDetails a inner join tbl_Accounts b on a.RegisterNo=b.RegisterNo
						inner join tbl_Branches c on a.BranchCode=c.BranchCode
						inner join tbl_Durations d on a.AccountLoanCode=d.AccountLoanCode
go
----WRITES STORE PROCEDURE-----
/*==================================*/
----WRITE STORE PROCEDURE FOR ACCOUNT
create proc getAccountList
as
begin 
	select * from tbl_Accounts
end
go

create proc insertAccount
@registerNo int output,
@registerDate datetime,
@name nvarchar(50),
@phone nvarchar(50),
@address nvarchar(50),
@email nvarchar(50),
@organization nvarchar(50),
@salary money
as
begin 
insert into tbl_Accounts values(@registerDate,@name,@phone,@address,@email,@organization,
								@salary);
set @registerNo=@@IDENTITY;								
end
go

create proc checkEmailExist
@email nvarchar(100)
as
begin 
select * from tbl_Accounts where Email=@email
end
go

create proc deleteDuration
@registerNo int
as
begin
declare @accountLoanCode int
delete from tbl_DurationDetails 
			where AccountLoanCode in (select AccountLoanCode from tbl_Durations
			where AccountLoanCode in (select AccountLoanCode from tbl_AccountDetails
			where RegisterNo in (select RegisterNo from tbl_Accounts 
			where RegisterNo=@registerNo)))
select @accountLoanCode=AccountLoanCode from tbl_AccountDetails where RegisterNo=@registerNo
delete from tbl_AccountDetails 
			where RegisterNo in (select RegisterNo from tbl_Accounts
			where RegisterNo=@registerNo)

delete from tbl_Durations 
			where AccountLoanCode=@accountLoanCode
			--where AccountLoanCode in(select AccountLoanCode from tbl_AccountDetails
			--where RegisterNo in (select RegisterNo from tbl_Accounts
			--where RegisterNo=@registerNo))
delete from tbl_Accounts 
			where RegisterNo=@registerNo
end
go
exec deleteDuration 36
go
---WRITE PROCEDURE FOR MANAGE-----
create proc getAdminList
as
begin 
select * from tbl_Admin
end
go

create proc insertAdmin
@adminID int output,
@adminAccount nvarchar(50),
@adminPass nvarchar(50),
@fullName nvarchar(70),
@address nvarchar(70),
@phone nvarchar(20),
@email nvarchar(80),
@roleCode int
as
begin
insert into tbl_Admin values(@adminAccount,@adminPass,@fullName,@address,@phone,@email,@roleCode);
set @adminID=@@IDENTITY;
end
go

create proc deleteAdmin
@adminID int
as
begin
delete from tbl_Admin where AdminID=@adminID
end
go

create proc checkLoginAdmin
@adminAccount nvarchar(50),
@adminPass nvarchar(50)
as 
begin 
select * from tbl_Admin where AdminAccount=@adminAccount and AdminPass=@adminPass
end
go

create proc editProfile
@adminID int,
@fullName nvarchar(70),
@address nvarchar(70),
@phone nvarchar(20),
@email nvarchar(80)
as
begin
update tbl_Admin
set FullName=@fullName,Address=@address,Phone=@phone,Email=@email
where AdminID=@adminID
end
go

create proc changePassword
@adminID int,
@pass nvarchar(50)
as
begin
update tbl_Admin
set AdminPass=@pass
where AdminID=@adminID
end
go

create proc checkEmailAdmin
@email nvarchar(100)
as 
begin
select * from tbl_Admin where Email=@email
end
go
---WRITE PROCEDURE FOR BRANCH----
create proc getBranchList
as
begin 
select * from tbl_Branches
end
go

create proc insertBranch
@BranchCode int output,
@name nvarchar(50),
@address nvarchar(80),
@phone nvarchar(20),
@email nvarchar(100),
@status bit
as
begin 
	insert into tbl_Branches values(@name,@address,@phone,@email,@status);
	set @BranchCode=@@IDENTITY 
end
go

create proc updateBranch
@BranchCode int,
@name nvarchar(50),
@address nvarchar(80),
@phone nvarchar(20),
@email nvarchar(100),
@status bit
as 
begin 
	update tbl_Branches
	set Name=@name,Address=@address,Phone=@phone,Email=@email,Status=@status
	where BranchCode=@BranchCode
end
go

create proc deleteBranch
@BranchCode int
as
begin 
	delete from tbl_Branches where BranchCode=@BranchCode
end
go

create proc checkEmail
@email nvarchar(50)
as
begin
select * from tbl_Branches where Email=@email
end
go
---WRITE PROCEDURE FOR LOAN TYPE
create proc getLoanTypeList
as
begin 
select * from tbl_LoanType
end
go

create proc insertLoanType
@loanTypeCode int output,
@name nvarchar(50),
@description nvarchar(100),
@interestRate float,
@status int
as
begin
insert into tbl_LoanType values(@name,@description,@interestRate,@status);
set @loanTypeCode=@@IDENTITY;
insert into tbl_Roles values(@name,@loanTypeCode);
end
go

create proc updateLoanType
@loanTypeCode int ,
@name nvarchar(50),
@description nvarchar(100),
@interestRate float,
@status int
as
begin
update tbl_LoanType
set Name=@name,Description=@description,InterestRate=@interestRate,Status=@status
where LoanTypeCode=@loanTypeCode
end
go

create proc deleteLoanType
@loanTypeCode int
as
begin 
delete from tbl_Roles where LoanTypeCode=@loanTypeCode
delete from tbl_LoanType where LoanTypeCode=@loanTypeCode
end
go

create proc checkLoanType
@name nvarchar(100)
as 
begin 
select * from tbl_LoanType where Name=@name
end
go
---WRITE PROCEDURE FOR DURATION-----
create proc getDurationList
as
begin 
select * from tbl_Durations
end
go

create proc insertCustomer
@registerNo int output,
@accountLoanCode int ,
@registerDate datetime,
@name nvarchar(50),
@phone nvarchar(20),
@address nvarchar(70),
@email nvarchar(100),
@organization nvarchar(100),
@salary float,
@branchCode int,
@period int
as
begin
insert into tbl_Accounts values(@registerDate,@name,@phone,@address,@email,@organization,@salary);
set @registerNo=@@IDENTITY;
insert into tbl_AccountDetails values(@registerNo,@accountLoanCode,@branchCode,@period);
end
go

SELECT DATEADD(month, 1, '2006-08-30')

go
create proc insertDuration
@accountLoanCode int output,
@loanTypeCode int,
@startDate datetime,
@amountLoan float,
@endDate datetime,
@balance float
as
begin
insert into tbl_Durations(LoanTypeCode,StartDate,AmountLoan,EndDate,Balance) values(@loanTypeCode,@startDate,@amountLoan,@endDate,@balance);
set @accountLoanCode=@@IDENTITY;
end
go
--WRITE PROCEDURE FOR ACCOUNTDETAILS
create proc getAccountDetailsList
as
begin 
	select * from tbl_AccountDetails
end
go
--WRITE PROCEDURE FOR DURATIONDETAILS
create proc getDurationDetailsList
@accountLoanCode int
as
begin
select * from tbl_DurationDetails 
where AccountLoanCode=@accountLoanCode
order by DateDue asc
end
go

create proc insertDurationDetails
@accountLoanCode int,
@fineCode int,
@dateDue datetime,
@amountDue float,
@amountPaid float,
@paidDate datetime
as
begin
insert into tbl_DurationDetails(AccountLoanCode,FineCode,DateDue,AmountDue,AmountPaid,PaidDate)
values(@accountLoanCode,@fineCode,@dateDue,@amountDue,@amountPaid,@paidDate);
end
go
----WRITE PROCEDURE FOR FINISH DURATION
create proc updateDurationHistory
@accountLoanCode int,
@fineCode int,
@dateDue datetime,
@amountDue float,
@amountPaid float,
@paidDate datetime,
@status bit
as
begin
update tbl_DurationDetails
set FineCode=@fineCode,AmountDue=@amountDue,AmountPaid=@amountPaid,
	PaidDate=@paidDate,Status=@status
where AccountLoanCode=@accountLoanCode and Year(DateDue)=Year(@dateDue) and MONTH(DateDue)=MONTH(@dateDue) and DAY(DateDue)=DAY(@dateDue)
end
go
/*exec updateDurationHistory 1,1,'2012-03-18 00:44:17.593',0,18333.33,'2012-3-19',1*/
--WRITE PROCEDURE FOR ROLES--
create proc getRolesList
as
begin
select * from tbl_Roles
end
go

---WRITES PROCEDURE FOR SEARCH CUSTOMER
create proc searchCustomerWithAccount
@registerNo int
as 
begin 
select * from tbl_Accounts where RegisterNo=@registerNo
end
go

create proc searchCustomerWithLoanAmount
@loanAmount float
as
begin
select distinct a.Period,b.Organization,b.RegisterDate,b.RegisterNo,b.Name,b.Address,b.Email,b.[Gross Salary],b.Phone,c.Name as [Branch Name],
				c.Status,d.AmountLoan,d.LoanStatus,d.Accepted
from tbl_AccountDetails a inner join tbl_Accounts b on a.RegisterNo=b.RegisterNo
						inner join tbl_Branches c on a.BranchCode=c.BranchCode
						inner join tbl_Durations d on a.AccountLoanCode=d.AccountLoanCode
where d.AmountLoan=@loanAmount						
end
go

create proc searchCustomerWithPeriod
@period int
as
begin
select distinct a.Period,b.Organization,b.RegisterDate,b.RegisterNo,b.Name,b.Address,b.Email,b.[Gross Salary],b.Phone,c.Name as [Branch Name],
				c.Status,d.AmountLoan,d.LoanStatus,d.Accepted
from tbl_AccountDetails a inner join tbl_Accounts b on a.RegisterNo=b.RegisterNo
						inner join tbl_Branches c on a.BranchCode=c.BranchCode
						inner join tbl_Durations d on a.AccountLoanCode=d.AccountLoanCode
where a.Period=@period						
end
go


